﻿USE [SyncSamplesDb_ChangeTracking_Custom]
GO
/****** Object:  Schema [Sales]    Script Date: 11/25/2009 07:26:06 ******/
CREATE SCHEMA [Sales] AUTHORIZATION [dbo]
GO
/****** Object:  Table [Sales].[Customer]    Script Date: 11/25/2009 07:26:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Sales].[Customer](
	[CustomerId] [uniqueidentifier] NOT NULL,
	[CustomerName] [nvarchar](100) NOT NULL,
	[SalesPerson] [nvarchar](100) NOT NULL,
	[CustomerType] [nvarchar](100) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [Sales].[Vendor]    Script Date: 11/25/2009 07:26:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Sales].[Vendor](
	[VendorId] [uniqueidentifier] NOT NULL,
	[VendorName] [nvarchar](100) NOT NULL,
	[CreditRating] [tinyint] NOT NULL,
	[PreferredVendor] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[VendorId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [Sales].[CustomerContact]    Script Date: 11/25/2009 07:26:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Sales].[CustomerContact](
	[CustomerId] [uniqueidentifier] NOT NULL,
	[PhoneNumber] [nvarchar](100) NOT NULL,
	[PhoneType] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_CustomerContact] PRIMARY KEY CLUSTERED 
(
	[CustomerId] ASC,
	[PhoneType] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [Sales].[OrderHeader]    Script Date: 11/25/2009 07:26:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Sales].[OrderHeader](
	[OrderId] [uniqueidentifier] NOT NULL,
	[CustomerId] [uniqueidentifier] NOT NULL,
	[OrderDate] [datetime] NOT NULL,
	[OrderStatus] [nvarchar](100) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[OrderId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [Sales].[OrderDetail]    Script Date: 11/25/2009 07:26:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Sales].[OrderDetail](
	[OrderDetailId] [int] NOT NULL,
	[OrderId] [uniqueidentifier] NOT NULL,
	[Product] [nvarchar](100) NOT NULL,
	[Quantity] [int] NOT NULL,
 CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED 
(
	[OrderDetailId] ASC,
	[OrderId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[usp_InsertSampleData]    Script Date: 11/25/2009 07:26:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------------------------------------
-- Insert test data.
--
--

--Wrap the inserts in a procedure so that each snippet
--can call the procedure to reset the database after
--the snippet completes.
CREATE PROCEDURE [dbo].[usp_InsertSampleData]

AS

	SET NOCOUNT ON

	DELETE FROM SyncSamplesDb_ChangeTracking.Sales.Vendor
	DELETE FROM SyncSamplesDb_ChangeTracking.Sales.OrderDetail
	DELETE FROM SyncSamplesDb_ChangeTracking.Sales.OrderHeader
	DELETE FROM SyncSamplesDb_ChangeTracking.Sales.CustomerContact
	DELETE FROM SyncSamplesDb_ChangeTracking.Sales.Customer

	--Insert into Customer.
	INSERT INTO SyncSamplesDb_ChangeTracking.Sales.Customer (CustomerName, SalesPerson, CustomerType) VALUES (N'Aerobic Exercise Company', N'James Bailey', N'Wholesale')
	INSERT INTO SyncSamplesDb_ChangeTracking.Sales.Customer (CustomerName, SalesPerson, CustomerType) VALUES (N'Exemplary Cycles', N'James Bailey', N'Retail')
	INSERT INTO SyncSamplesDb_ChangeTracking.Sales.Customer (CustomerName, SalesPerson, CustomerType) VALUES (N'Tandem Bicycle Store', N'Brenda Diaz', N'Wholesale')
	INSERT INTO SyncSamplesDb_ChangeTracking.Sales.Customer (CustomerName, SalesPerson, CustomerType) VALUES (N'Rural Cycle Emporium', N'Brenda Diaz', N'Retail')
	INSERT INTO SyncSamplesDb_ChangeTracking.Sales.Customer (CustomerName, SalesPerson, CustomerType) VALUES (N'Sharp Bikes', N'Brenda Diaz', N'Retail')

	--Declare variables that are used in subsequent inserts.
	DECLARE @CustomerId uniqueidentifier
	DECLARE @OrderId uniqueidentifier
	DECLARE @InsertString nvarchar(1024)

	----------------------------------
	-------- First Customer ----------
	----------------------------------
	--No additional inserts for Aerobic Exercise Company


	----------------------------------
	------- Second Customer ----------
	----------------------------------
	--Insert into CustomerContact.
	SELECT @CustomerId = CustomerId FROM SyncSamplesDb_ChangeTracking.Sales.Customer WHERE CustomerName = N'Exemplary Cycles'
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.CustomerContact (CustomerId, PhoneNumber, PhoneType) VALUES (''' + CAST(@CustomerId AS nvarchar(38)) + ''', ''959-555-0151'', ''Business'')'
	EXECUTE sp_executesql @InsertString

	--Insert into OrderHeader.
	-- First order
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.OrderHeader (CustomerId, OrderDate, OrderStatus) VALUES (''' + CAST(@CustomerId AS nvarchar(38)) + ''', ''2007-01-28'', ''Complete'')'
	EXECUTE sp_executesql @InsertString
	-- Second order
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.OrderHeader (CustomerId, OrderDate, OrderStatus) VALUES (''' + CAST(@CustomerId AS nvarchar(38)) + ''', ''2007-02-03'', ''Pending'')'
	EXECUTE sp_executesql @InsertString

	--Insert into OrderDetail.
	-- First order details
	SELECT @OrderId = OrderId FROM SyncSamplesDb_ChangeTracking.Sales.OrderHeader WHERE OrderDate = N'2007-01-28'
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.OrderDetail (OrderDetailId, OrderId, Product, Quantity) VALUES (1, ''' + CAST(@OrderId AS nvarchar(38)) + ''', ''Chain'', 3)'
	EXECUTE sp_executesql @InsertString
	-- Second order details
	SELECT @OrderId = OrderId FROM SyncSamplesDb_ChangeTracking.Sales.OrderHeader WHERE OrderDate = N'2007-02-03'
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.OrderDetail (OrderDetailId, OrderId, Product, Quantity) VALUES (1, ''' + CAST(@OrderId AS nvarchar(38)) + ''', ''Mountain Tire'', 7)'
	EXECUTE sp_executesql @InsertString

	----------------------------------
	-------- Third Customer ----------
	----------------------------------
	--Insert into CustomerContact.
	SELECT @CustomerId = CustomerId FROM SyncSamplesDb_ChangeTracking.Sales.Customer WHERE CustomerName = N'Tandem Bicycle Store'
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.CustomerContact (CustomerId, PhoneNumber, PhoneType) VALUES (''' + CAST(@CustomerId AS nvarchar(38)) + ''', ''107-555-0138'', ''Business'')'
	EXECUTE sp_executesql @InsertString

	--Insert into OrderHeader.
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.OrderHeader (CustomerId, OrderDate, OrderStatus) VALUES (''' + CAST(@CustomerId AS nvarchar(38)) + ''', ''2007-02-04'', ''Complete'')'
	EXECUTE sp_executesql @InsertString

	--Insert into OrderDetail.
	SELECT @OrderId = OrderId FROM SyncSamplesDb_ChangeTracking.Sales.OrderHeader WHERE OrderDate = N'2007-02-04'
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.OrderDetail (OrderDetailId, OrderId, Product, Quantity) VALUES (1, ''' + CAST(@OrderId AS nvarchar(38)) + ''', ''Road Pedal'', 4)'
	EXECUTE sp_executesql @InsertString
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.OrderDetail (OrderDetailId, OrderId, Product, Quantity) VALUES (2, ''' + CAST(@OrderId AS nvarchar(38)) + ''', ''Road Rear Wheel'', 4)'
	EXECUTE sp_executesql @InsertString

	----------------------------------
	------- Fourth Customer ----------
	----------------------------------
	--Insert into CustomerContact.
	SELECT @CustomerId = CustomerId FROM SyncSamplesDb_ChangeTracking.Sales.Customer WHERE CustomerName = N'Rural Cycle Emporium'
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.CustomerContact (CustomerId, PhoneNumber, PhoneType) VALUES (''' + CAST(@CustomerId AS nvarchar(38)) + ''', ''158-555-0142'', ''Business'')'
	EXECUTE sp_executesql @InsertString

	--Insert into CustomerContact (second contact info).
	SELECT @CustomerId = CustomerId FROM SyncSamplesDb_ChangeTracking.Sales.Customer WHERE CustomerName = N'Rural Cycle Emporium'
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.CustomerContact (CustomerId, PhoneNumber, PhoneType) VALUES (''' + CAST(@CustomerId AS nvarchar(38)) + ''', ''453-555-0167'', ''Mobile'')'
	EXECUTE sp_executesql @InsertString

	--Insert into OrderHeader.
	-- First order
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.OrderHeader (CustomerId, OrderDate, OrderStatus) VALUES (''' + CAST(@CustomerId AS nvarchar(38)) + ''', ''2007-03-12'', ''Complete'')'
	EXECUTE sp_executesql @InsertString
	-- Second order
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.OrderHeader (CustomerId, OrderDate, OrderStatus) VALUES (''' + CAST(@CustomerId AS nvarchar(38)) + ''', ''2007-04-14'', ''Back Ordered'')'
	EXECUTE sp_executesql @InsertString

	--Insert into OrderDetail.
	--First order details
	SELECT @OrderId = OrderId FROM SyncSamplesDb_ChangeTracking.Sales.OrderHeader WHERE OrderDate = N'2007-03-12'
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.OrderDetail (OrderDetailId, OrderId, Product, Quantity) VALUES (1, ''' + CAST(@OrderId AS nvarchar(38)) + ''', ''Hydration Pack'', 1)'
	EXECUTE sp_executesql @InsertString
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.OrderDetail (OrderDetailId, OrderId, Product, Quantity) VALUES (2, ''' + CAST(@OrderId AS nvarchar(38)) + ''', ''Men''''s Sports Shorts'', 3)'
	EXECUTE sp_executesql @InsertString
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.OrderDetail (OrderDetailId, OrderId, Product, Quantity) VALUES (3, ''' + CAST(@OrderId AS nvarchar(38)) + ''', ''Water Bottle'', 6)'
	EXECUTE sp_executesql @InsertString
	--Second order details
	SELECT @OrderId = OrderId FROM SyncSamplesDb_ChangeTracking.Sales.OrderHeader WHERE OrderDate = N'2007-04-14'
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.OrderDetail (OrderDetailId, OrderId, Product, Quantity) VALUES (1, ''' + CAST(@OrderId AS nvarchar(38)) + ''', ''Mountain Tire'', 5)'
	EXECUTE sp_executesql @InsertString
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.OrderDetail (OrderDetailId, OrderId, Product, Quantity) VALUES (2, ''' + CAST(@OrderId AS nvarchar(38)) + ''', ''Women''''s Mountain Shorts'', 5)'
	EXECUTE sp_executesql @InsertString
	SET @InsertString = 'INSERT INTO SyncSamplesDb_ChangeTracking.Sales.OrderDetail (OrderDetailId, OrderId, Product, Quantity) VALUES (3, ''' + CAST(@OrderId AS nvarchar(38)) + ''', ''Road Pedal'', 2)'
	EXECUTE sp_executesql @InsertString

	----------------------------------
	-------- Fifth Customer ----------
	----------------------------------
	--No additional inserts for Sharp Bikes


	
	--Insert into Vendor.
	INSERT INTO SyncSamplesDb_ChangeTracking.Sales.Vendor (VendorName, CreditRating, PreferredVendor) VALUES (N'Premier Sport, Inc.', 2, 1)
	INSERT INTO SyncSamplesDb_ChangeTracking.Sales.Vendor (VendorName, CreditRating, PreferredVendor) VALUES (N'Metro Sport Equipment', 1, 1)
	INSERT INTO SyncSamplesDb_ChangeTracking.Sales.Vendor (VendorName, CreditRating, PreferredVendor) VALUES (N'Mountain Works', 3, 0)
	INSERT INTO SyncSamplesDb_ChangeTracking.Sales.Vendor (VendorName, CreditRating, PreferredVendor) VALUES (N'Green Lake Bike Company', 2, 1)
	INSERT INTO SyncSamplesDb_ChangeTracking.Sales.Vendor (VendorName, CreditRating, PreferredVendor) VALUES (N'Compete, Inc.', 5, 0)


	SET NOCOUNT OFF
GO
/****** Object:  Default [DF__Customer__Custom__014935CB]    Script Date: 11/25/2009 07:26:07 ******/
ALTER TABLE [Sales].[Customer] ADD  DEFAULT (newid()) FOR [CustomerId]
GO
/****** Object:  Default [DF__Vendor__VendorId__108B795B]    Script Date: 11/25/2009 07:26:07 ******/
ALTER TABLE [Sales].[Vendor] ADD  DEFAULT (newid()) FOR [VendorId]
GO
/****** Object:  Default [DF__OrderHead__Order__07F6335A]    Script Date: 11/25/2009 07:26:07 ******/
ALTER TABLE [Sales].[OrderHeader] ADD  DEFAULT (newid()) FOR [OrderId]
GO
/****** Object:  Default [DF__OrderHead__Order__08EA5793]    Script Date: 11/25/2009 07:26:07 ******/
ALTER TABLE [Sales].[OrderHeader] ADD  DEFAULT (getdate()) FOR [OrderDate]
GO
/****** Object:  Default [DF__OrderDeta__Quant__0BC6C43E]    Script Date: 11/25/2009 07:26:07 ******/
ALTER TABLE [Sales].[OrderDetail] ADD  DEFAULT ((1)) FOR [Quantity]
GO
/****** Object:  ForeignKey [FK_CustomerContact_Customer]    Script Date: 11/25/2009 07:26:07 ******/
ALTER TABLE [Sales].[CustomerContact]  WITH CHECK ADD  CONSTRAINT [FK_CustomerContact_Customer] FOREIGN KEY([CustomerId])
REFERENCES [Sales].[Customer] ([CustomerId])
GO
ALTER TABLE [Sales].[CustomerContact] CHECK CONSTRAINT [FK_CustomerContact_Customer]
GO
/****** Object:  ForeignKey [FK_OrderHeader_Customer]    Script Date: 11/25/2009 07:26:07 ******/
ALTER TABLE [Sales].[OrderHeader]  WITH CHECK ADD  CONSTRAINT [FK_OrderHeader_Customer] FOREIGN KEY([CustomerId])
REFERENCES [Sales].[Customer] ([CustomerId])
GO
ALTER TABLE [Sales].[OrderHeader] CHECK CONSTRAINT [FK_OrderHeader_Customer]
GO
/****** Object:  ForeignKey [FK_OrderDetail_OrderHeader]    Script Date: 11/25/2009 07:26:07 ******/
ALTER TABLE [Sales].[OrderDetail]  WITH CHECK ADD  CONSTRAINT [FK_OrderDetail_OrderHeader] FOREIGN KEY([OrderId])
REFERENCES [Sales].[OrderHeader] ([OrderId])
GO
ALTER TABLE [Sales].[OrderDetail] CHECK CONSTRAINT [FK_OrderDetail_OrderHeader]
GO
